package com.wosai.register.service;

import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.ColumnMapRowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.stereotype.Service;

import com.wosai.register.model.BranchExpend;
import com.wosai.register.model.BranchExpendSubject;
import com.wosai.register.model.BranchExpendType;
import com.wosai.register.util.StringUtil;

@Service
public class JdbcQueryService implements QueryService {
    
    private static final String AGGREGATE_SELL = "select sum(total) as daily_sell from ORDER2 where merchant_id like :merchantId and branch_id like :branchId and terminal_id like :terminalId and date_paid >= :startDate and date_paid < :endDate and state = '2' "; 
    private static final String AGGREGATE_RETURN = "select sum(total) as daily_return from ORDER2 where merchant_id like :merchantId and branch_id like :branchId and terminal_id like :terminalId and date_paid >= :startDate and date_paid < :endDate and state in ('0', '1')"; 

    private static final String CASH_PAYMENT = "select sum(total) as daily_cash_payment from ORDER2 where merchant_id like :merchantId and branch_id like :branchId and terminal_id like :terminalId and date_paid >= :startDate and date_paid < :endDate and type = '1' "; 
    private static final String ATM_PAYMENT = "select sum(total) as daily_card_payment from ORDER2 where merchant_id like :merchantId and branch_id like :branchId and terminal_id like :terminalId and date_paid >= :startDate and date_paid < :endDate and type = '2' "; 
    private static final String MEMBER_CARD_PAYMENT = "select sum(total) as daily_member_card_payment from ORDER2 where merchant_id like :merchantId and branch_id like :branchId and terminal_id like :terminalId and date_paid >= :startDate and date_paid < :endDate and type = '3' "; 

    private static final String SELL_ORDERS = "select id as order_id, discount, total as total_pay, type as pay_type, branch_id as branch_id, terminal_id as term_id, date_paid as pay_time from ORDER2 where merchant_id like :merchantId and branch_id like :branchId and terminal_id like :terminalId and date_paid >= :startDate and date_paid < :endDate and state = '2' limit :offset, :size"; 
    private static final String COUNT_SELL_ORDERS = "select count(*) from ORDER2 where merchant_id like :merchantId and branch_id like :branchId and terminal_id like :terminalId and date_paid >= :startDate and date_paid < :endDate and state = '2' "; 
//    private static final String AGGREGATE_SELL_PAGE = "select sum(total) as daily_sell from ORDER2 where merchant_id=:merchantId and branch_id=:branchId and terminal_id=:terminalId and date_paid like :datePaid limit :offset, :size "; 

    private static final String RETURN_ORDERS = "select id as order_id, discount, total as total_pay, type as pay_type, branch_id as branch_id, terminal_id as term_id, date_paid as pay_time from ORDER2 where merchant_id like :merchantId and branch_id like :branchId and terminal_id like :terminalId and date_paid >= :startDate and date_paid < :endDate and state in ('1', '0') limit :offset, :size";
    private static final String COUNT_RETURN_ORDERS = "select count(*) from ORDER2 where merchant_id like :merchantId and branch_id like :branchId and terminal_id like :terminalId and date_paid >= :startDate and date_paid < :endDate and state in ('1', '0') ";
//    private static final String AGGREGATE_RETURN_PAGE = "select sum(total) as daily_return from ORDER2 where merchant_id=:merchantId and branch_id=:branchId and terminal_id=:terminalId and date_returned like :dateReturned limit :offset, :size "; 
    
    private static final String ORDER_ITEMS = "select product_name as goods_name, price as goods_price, num, subtotal as total_pay from ORDER_ITEM where merchant_id=:merchantId and branch_id=:branchId and terminal_id=:terminalId and order_id=:orderId "; 

    private static final String TOP_10_BY_VOLUME = StringUtil.readInputStream(JdbcQueryService.class.getResourceAsStream("top_10_by_volume.sql"));

    private static final String TOP_10_BY_REVENUE = StringUtil.readInputStream(JdbcQueryService.class.getResourceAsStream("top_10_by_revenue.sql"));
    
    private static final String TOP_CATEGORIES_PER_BRANCH = StringUtil.readInputStream(JdbcQueryService.class.getResourceAsStream("top_categories_per_branch.sql"));

    private static final String TOP_PRODUCTS_PER_BRANCH = StringUtil.readInputStream(JdbcQueryService.class.getResourceAsStream("top_products_per_branch.sql"));

    private static final String SHIFTS_PER_BRANCH = StringUtil.readInputStream(JdbcQueryService.class.getResourceAsStream("shifts_per_branch.sql"));
    
    private static final String BRANCH_EXPENDS = "select * from BRANCH_EXPEND where merchant_id like :merchantId and branch_id like :branchId and terminal_id like :terminalId and is_deleted = '0' ";

    private static final String BRANCH_EXPEND_TYPES = "select * from BRANCH_EXPEND_TYPE where merchant_id like :merchantId and branch_id like :branchId and terminal_id like :terminalId and is_deleted = '0' ";

    private static final String BRANCH_EXPEND_SUBJECTS = "select * from BRANCH_EXPEND_SUBJECT where merchant_id like :merchantId and branch_id like :branchId and terminal_id like :terminalId and is_deleted = '0' ";

    private static final ColumnMapRowMapper columnMapRowMapper = new ColumnMapRowMapper();
    
    NamedParameterJdbcTemplate npjt;
    
    @Autowired
    public JdbcQueryService(DataSource dataSource){
        this.npjt = new NamedParameterJdbcTemplate(dataSource);
        
    }
    
    @Override
    public Map<String, Object> findAggregatedSellAndReturn(String merchantId,
                                                           String branchId,
                                                           String terminalId,
                                                           String startDate,
                                                           String endDate) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("merchantId", merchantId);
        params.put("branchId", branchId);
        params.put("terminalId", terminalId);
        params.put("startDate", startDate);
        params.put("endDate", endDate);
        
        Double aggregatedSell = this.npjt.queryForObject(AGGREGATE_SELL, params, Double.class);
        aggregatedSell = aggregatedSell==null? 0: aggregatedSell;
        Double aggregatedReturn = this.npjt.queryForObject(AGGREGATE_RETURN, params, Double.class);
        aggregatedReturn = aggregatedReturn==null? 0: aggregatedReturn;
        
        Map<String, Object> result = new HashMap<String, Object>();
        result.put("aggregatedSell", aggregatedSell);
        result.put("aggregatedReturn", aggregatedReturn);

        int saleTotalCount = this.npjt.queryForObject(COUNT_SELL_ORDERS, params, Integer.class);
        result.put("saleTotalCount", saleTotalCount);

        int returnTotalCount = this.npjt.queryForObject(COUNT_RETURN_ORDERS, params, Integer.class);
        result.put("returnTotalCount", returnTotalCount);

        return result;
    }

    @Override
    public Map<String, Object> findPaymentByType(String merchantId,
                                                 String branchId,
                                                 String terminalId,
                                                 String startDate,
                                                 String endDate) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("merchantId", merchantId);
        params.put("branchId", branchId);
        params.put("terminalId", terminalId);
        params.put("startDate", startDate);
        params.put("endDate", endDate);
        
        Double cashPayment = this.npjt.queryForObject(CASH_PAYMENT, params, Double.class);
        cashPayment = cashPayment==null? 0: cashPayment;
        Double atmPayment = this.npjt.queryForObject(ATM_PAYMENT, params, Double.class);
        atmPayment = atmPayment==null? 0: atmPayment;
        Double memberCardPayment = this.npjt.queryForObject(MEMBER_CARD_PAYMENT, params, Double.class);
        memberCardPayment = memberCardPayment==null? 0: memberCardPayment;
        
        Map<String, Object> result = new HashMap<String, Object>();
        result.put("cashPayment", cashPayment);
        result.put("atmPayment", atmPayment);
        result.put("memberCardPayment", memberCardPayment);
        
        return result;
    }

    @Override
    public Map<String, Object> findOrders(String merchantId,
                                          String branchId,
                                          String terminalId,
                                          int offset,
                                          int size,
                                          String startDate,
                                          String endDate) {
        
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("merchantId", merchantId);
        params.put("branchId", branchId);
        params.put("terminalId", terminalId);
        params.put("startDate", startDate);
        params.put("endDate", endDate);
        params.put("offset", offset);
        params.put("size", size);
        
        List<Map<String,Object>> orders = this.npjt.query(SELL_ORDERS, params, columnMapRowMapper);
        
        double aggregatedSell = 0;
        for(Map<String,Object> order: orders)
            aggregatedSell += Double.parseDouble((String)order.get("total_pay"));
        
        Map<String, Object> result = new HashMap<String, Object>();
        result.put("aggregatedSell", aggregatedSell);
        result.put("orders", orders);
        
        Integer count = this.npjt.queryForObject(COUNT_SELL_ORDERS, params, Integer.class);
        result.put("totalCount", count);
        
        return result;
    }

    @Override
    public Map<String, Object> findReturnedOrders(String merchantId,
                                                  String branchId,
                                                  String terminalId,
                                                  int offset, int size,
                                                  String startDate,
                                                  String endDate) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("merchantId", merchantId);
        params.put("branchId", branchId);
        params.put("terminalId", terminalId);
        params.put("startDate", startDate);
        params.put("endDate", endDate);
        params.put("offset", offset);
        params.put("size", size);
        
        List<Map<String,Object>> orders = this.npjt.query(RETURN_ORDERS, params, columnMapRowMapper);
        
        double aggregatedReturn = 0;
        for(Map<String,Object> order: orders)
            aggregatedReturn += Double.parseDouble((String)order.get("total_pay"));
        
        Map<String, Object> result = new HashMap<String, Object>();
        result.put("aggregatedReturn", aggregatedReturn);
        result.put("orders", orders);
        
        Integer count = this.npjt.queryForObject(COUNT_RETURN_ORDERS, params, Integer.class);
        result.put("returnTotalCount", count);
        return result;
    }

    @Override
    public Map<String, Object> findOrderItems(String merchantId,
                                              String branchId,
                                              String terminalId, String orderId) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("merchantId", merchantId);
        params.put("branchId", branchId);
        params.put("terminalId", terminalId);
        params.put("orderId", orderId);
        List<Map<String,Object>> orderItems = this.npjt.query(ORDER_ITEMS, params, columnMapRowMapper);
        
        return Collections.<String,Object>singletonMap("orderItems", orderItems);
    }

    @Override
    public Map<String, Object> findTop10ByVolume(String merchantId,
                                                 String startDate,
                                                 String endDate) {
        
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("merchantId", merchantId);
        params.put("startDate", startDate);
        params.put("endDate", endDate);
        
        List<Map<String,Object>> top10 = this.npjt.query(TOP_10_BY_VOLUME, params, columnMapRowMapper);
        return Collections.<String,Object>singletonMap("top10", top10);
    }

    @Override
    public Map<String, Object> findTop10ByRevenue(String merchantId,
                                                  String startDate,
                                                  String endDate) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("merchantId", merchantId);
        params.put("startDate", startDate);
        params.put("endDate", endDate);
        
        List<Map<String,Object>> top10 = this.npjt.query(TOP_10_BY_REVENUE, params, columnMapRowMapper);
        return Collections.<String,Object>singletonMap("top10", top10);
    }

    @Override
    public Map<String, Object> topCategoriesByRevenuePerBranch(String merchantId,
                                                               String branchId,
                                                               String startDate,
                                                               String endDate) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("merchantId", merchantId);
        params.put("branchId", branchId);
        params.put("startDate", startDate);
        params.put("endDate", endDate);
        
        List<Map<String,Object>> categories = this.npjt.query(TOP_CATEGORIES_PER_BRANCH, params, columnMapRowMapper);
        return Collections.<String,Object>singletonMap("categorySort", categories);
    }

    @Override
    public Map<String, Object> topProductsByRevenuePerBranch(String merchantId,
                                                             String branchId,
                                                             String startDate,
                                                             String endDate) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("merchantId", merchantId);
        params.put("branchId", branchId);
        params.put("startDate", startDate);
        params.put("endDate", endDate);
        
        List<Map<String,Object>> products = this.npjt.query(TOP_PRODUCTS_PER_BRANCH, params, columnMapRowMapper);
        return Collections.<String,Object>singletonMap("productSort", products);
    }

    @Override
    public Map<String, Object> findShifts(String merchantId,
                                          String branchId,
                                          String startDate,
                                          String endDate) {
        
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("merchantId", merchantId);
        params.put("branchId", branchId);
        params.put("startDate", startDate);
        params.put("endDate", endDate);
        
        List<Map<String,Object>> shifts = this.npjt.query(SHIFTS_PER_BRANCH, params, columnMapRowMapper);
        return Collections.<String,Object>singletonMap("shifts", shifts);
    }
    
    @Override
    public Map<String, Object> findBranchExpends(String merchantId,
                                                 String branchId,
                                                 String terminalId,
                                                 String startDate,
                                                 String endDate) {
        
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("merchantId", merchantId);
        params.put("branchId", branchId);
        params.put("terminalId", terminalId);
        
        String sql = BRANCH_EXPENDS;
        if (startDate != null){
            sql += " and expend_date >= :startDate";
            params.put("startDate", startDate);
        }

        if (endDate != null){
            sql += " and expend_date < :endDate";
            params.put("endDate", endDate);

        }

        List<BranchExpend> expends = this.npjt.query(sql, params, ParameterizedBeanPropertyRowMapper.newInstance(BranchExpend.class));
        
        List<BranchExpendType> types = this.npjt.query(BRANCH_EXPEND_TYPES, params, ParameterizedBeanPropertyRowMapper.newInstance(BranchExpendType.class));
        
        List<BranchExpendSubject> subjects = this.npjt.query(BRANCH_EXPEND_SUBJECTS, params, ParameterizedBeanPropertyRowMapper.newInstance(BranchExpendSubject.class));
        
        Map<String, Object> result = new HashMap<String, Object>();
        result.put("expends", expends);
        result.put("expendTypes", types);
        result.put("expendSubjects", subjects);
        
        return result;
    }

}
